const mysql = require('mysql2/promise');

// 数据库连接配置
const dbConfig = {
  host: '43.143.253.188',
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4',
  port: 3306,
  connectTimeout: 60000
};

// 需要迁移的表列表（根据检查结果）
const tablesToMigrate = [
  'announcements',
  'basic_items',
  'casino_config',
  'character_attribute_points',
  'character_equipment_inventory',
  'character_inventory',
  'character_locations',
  'character_medicine',
  'character_mounts',
  'character_residences',
  'character_stones',
  'characters',
  'chat_messages',
  'faction_members',
  'factions',
  'gem_attribute',
  'gem_base',
  'items',
  'map_areas',
  'monthly_cards',
  'mount_base',
  'mount_training',
  'player_online_time',
  'private_messages',
  'social_relations',
  'system_messages'
];

// 特殊处理的表（shop_goods 是混合状态）
const specialTables = [
  'shop_goods'
];

function buildColumnDefinition (columnDef) {
  let definition = columnDef.COLUMN_TYPE;

  // 处理 NULL/NOT NULL
  if (columnDef.IS_NULLABLE === 'NO') {
    definition += ' NOT NULL';
  }

  // 处理默认值
  if (columnDef.COLUMN_DEFAULT) {
    if (columnDef.COLUMN_DEFAULT === 'CURRENT_TIMESTAMP') {
      definition += ' DEFAULT CURRENT_TIMESTAMP';
    } else if (columnDef.COLUMN_DEFAULT === 'NULL') {
      definition += ' DEFAULT NULL';
    } else {
      definition += ` DEFAULT '${columnDef.COLUMN_DEFAULT}'`;
    }
  }

  // 处理 EXTRA 属性
  if (columnDef.EXTRA) {
    if (columnDef.EXTRA.includes('on update CURRENT_TIMESTAMP')) {
      definition += ' ON UPDATE CURRENT_TIMESTAMP';
    } else if (columnDef.EXTRA.includes('auto_increment')) {
      definition += ' AUTO_INCREMENT';
    }
  }

  // 处理注释
  if (columnDef.COLUMN_COMMENT) {
    definition += ` COMMENT '${columnDef.COLUMN_COMMENT}'`;
  }

  return definition;
}

async function checkTableExists (connection, tableName) {
  try {
    const [rows] = await connection.execute(
      `SELECT COUNT(*) as count FROM information_schema.tables 
       WHERE table_schema = ? AND table_name = ?`,
      [dbConfig.database, tableName]
    );
    return rows[0].count > 0;
  } catch (error) {
    console.error(`检查表 ${tableName} 是否存在时出错:`, error.message);
    return false;
  }
}

async function checkColumnExists (connection, tableName, columnName) {
  try {
    const [rows] = await connection.execute(
      `SELECT COUNT(*) as count FROM information_schema.columns 
       WHERE table_schema = ? AND table_name = ? AND column_name = ?`,
      [dbConfig.database, tableName, columnName]
    );
    return rows[0].count > 0;
  } catch (error) {
    console.error(`检查列 ${tableName}.${columnName} 是否存在时出错:`, error.message);
    return false;
  }
}

async function getColumnDefinition (connection, tableName, columnName) {
  try {
    const [rows] = await connection.execute(
      `SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT
       FROM information_schema.columns 
       WHERE table_schema = ? AND table_name = ? AND column_name = ?`,
      [dbConfig.database, tableName, columnName]
    );
    return rows[0];
  } catch (error) {
    console.error(`获取列定义 ${tableName}.${columnName} 时出错:`, error.message);
    return null;
  }
}

async function migrateTable (connection, tableName) {
  console.log(`\n🔄 处理表: ${tableName}`);

  // 检查表是否存在
  const tableExists = await checkTableExists(connection, tableName);
  if (!tableExists) {
    console.log(`⚠️  表 ${tableName} 不存在，跳过`);
    return { success: true, skipped: true };
  }

  const migrations = [];

  // 处理 createdAt 字段
  const createdAtExists = await checkColumnExists(connection, tableName, 'createdAt');
  const created_atExists = await checkColumnExists(connection, tableName, 'created_at');

  if (createdAtExists && !created_atExists) {
    const columnDef = await getColumnDefinition(connection, tableName, 'createdAt');
    if (columnDef) {
      const newDefinition = buildColumnDefinition(columnDef);
      migrations.push({
        sql: `ALTER TABLE \`${tableName}\` CHANGE COLUMN \`createdAt\` \`created_at\` ${newDefinition}`,
        description: `重命名 createdAt -> created_at`
      });
    }
  } else if (created_atExists) {
    console.log(`✅ ${tableName}.created_at 已存在`);
  } else {
    console.log(`⚠️  ${tableName} 没有 createdAt 字段`);
  }

  // 处理 updatedAt 字段
  const updatedAtExists = await checkColumnExists(connection, tableName, 'updatedAt');
  const updated_atExists = await checkColumnExists(connection, tableName, 'updated_at');

  if (updatedAtExists && !updated_atExists) {
    const columnDef = await getColumnDefinition(connection, tableName, 'updatedAt');
    if (columnDef) {
      const newDefinition = buildColumnDefinition(columnDef);
      migrations.push({
        sql: `ALTER TABLE \`${tableName}\` CHANGE COLUMN \`updatedAt\` \`updated_at\` ${newDefinition}`,
        description: `重命名 updatedAt -> updated_at`
      });
    }
  } else if (updated_atExists) {
    console.log(`✅ ${tableName}.updated_at 已存在`);
  } else {
    console.log(`⚠️  ${tableName} 没有 updatedAt 字段`);
  }

  // 执行迁移
  let successCount = 0;
  for (const migration of migrations) {
    try {
      console.log(`   🔄 ${migration.description}`);
      console.log(`   SQL: ${migration.sql}`);
      await connection.execute(migration.sql);
      console.log(`   ✅ ${migration.description} 成功`);
      successCount++;
    } catch (error) {
      console.error(`   ❌ ${migration.description} 失败:`, error.message);
    }
  }

  return {
    success: successCount === migrations.length,
    total: migrations.length,
    successCount
  };
}

async function migrateSpecialTable (connection, tableName) {
  console.log(`\n🔄 处理特殊表: ${tableName}`);

  if (tableName === 'shop_goods') {
    // shop_goods 表有混合状态，需要特殊处理
    const createdAtExists = await checkColumnExists(connection, tableName, 'createdAt');
    const created_atExists = await checkColumnExists(connection, tableName, 'created_at');
    const updatedAtExists = await checkColumnExists(connection, tableName, 'updatedAt');
    const updated_atExists = await checkColumnExists(connection, tableName, 'updated_at');

    console.log(`   状态检查:`);
    console.log(`   - createdAt: ${createdAtExists ? '存在' : '不存在'}`);
    console.log(`   - created_at: ${created_atExists ? '存在' : '不存在'}`);
    console.log(`   - updatedAt: ${updatedAtExists ? '存在' : '不存在'}`);
    console.log(`   - updated_at: ${updated_atExists ? '存在' : '不存在'}`);

    // 如果还有驼峰字段，需要删除
    if (createdAtExists && created_atExists) {
      try {
        console.log(`   🔄 删除重复的 createdAt 字段`);
        await connection.execute(`ALTER TABLE \`${tableName}\` DROP COLUMN \`createdAt\``);
        console.log(`   ✅ 删除 createdAt 字段成功`);
      } catch (error) {
        console.error(`   ❌ 删除 createdAt 字段失败:`, error.message);
      }
    }

    if (updatedAtExists && updated_atExists) {
      try {
        console.log(`   🔄 删除重复的 updatedAt 字段`);
        await connection.execute(`ALTER TABLE \`${tableName}\` DROP COLUMN \`updatedAt\``);
        console.log(`   ✅ 删除 updatedAt 字段成功`);
      } catch (error) {
        console.error(`   ❌ 删除 updatedAt 字段失败:`, error.message);
      }
    }

    return { success: true, total: 0, successCount: 0 };
  }

  return { success: false, total: 0, successCount: 0 };
}

async function main () {
  console.log('🚀 开始迁移所有表的数据库字段...');
  console.log(`📋 目标数据库: ${dbConfig.host}/${dbConfig.database}`);
  console.log(`📊 需要处理的表数量: ${tablesToMigrate.length + specialTables.length}`);

  let connection;

  try {
    // 连接数据库
    console.log('\n🔌 连接数据库...');
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');

    // 执行迁移
    let totalSuccess = 0;
    let totalTables = 0;
    let skippedTables = 0;

    // 处理普通表
    for (const tableName of tablesToMigrate) {
      const result = await migrateTable(connection, tableName);
      totalTables++;

      if (result.skipped) {
        skippedTables++;
      } else if (result.success) {
        totalSuccess++;
      }
    }

    // 处理特殊表
    for (const tableName of specialTables) {
      const result = await migrateSpecialTable(connection, tableName);
      totalTables++;

      if (result.skipped) {
        skippedTables++;
      } else if (result.success) {
        totalSuccess++;
      }
    }

    // 输出统计结果
    console.log('\n📊 迁移完成统计:');
    console.log(`   总表数: ${totalTables}`);
    console.log(`   成功: ${totalSuccess}`);
    console.log(`   跳过: ${skippedTables}`);
    console.log(`   失败: ${totalTables - totalSuccess - skippedTables}`);

    if (totalSuccess > 0) {
      console.log('\n✅ 数据库字段迁移完成！');
      console.log('\n📝 接下来需要执行以下步骤：');
      console.log('1. 重启应用程序');
      console.log('2. 测试功能是否正常');
      console.log('3. 运行检查脚本验证迁移结果');
    } else {
      console.log('\n⚠️  没有执行任何迁移操作');
    }

  } catch (error) {
    console.error('\n❌ 迁移过程中发生错误:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n🔌 数据库连接已关闭');
    }
  }
}

// 执行主函数
main().catch(console.error);

